import pymysql
from flask import Flask, render_template, request, jsonify
from datetime import datetime

app = Flask(__name__)

# -----------------------------
# 数据库连接配置（示例）
# -----------------------------
DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = ""
DB_NAME = "gitlab_stats"


def get_db_connection():
    """
    获取数据库连接（使用pymysql）
    """
    return pymysql.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        db=DB_NAME,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


# =====================================
# 1. 单人新增行数排名 - 页面与数据接口
# =====================================

@app.route('/single_user_ranking')
def single_user_ranking():
    """
    返回单人新增行数排名页面（前端页面）。
    """
    return render_template('single_user_ranking.html')


@app.route('/api/single_user_data', methods=['GET'])
def single_user_data():
    """
    单人新增行数排名的数据接口，可支持多种筛选：
    - 时间范围（start_date, end_date）
    - 项目名称（project_name）
    - 作者姓名(或Email)
    - 按项目展示（show_by_project）
    """
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    project_name = request.args.get('project_name', '')
    author_name = request.args.get('author_name', '')
    show_by_project = request.args.get('show_by_project', False) == 'true'

    query = """
        SELECT 
            author_name,
            SUM(lines_added) as total_lines_added,
            COUNT(commit_id) as commit_count
    """
    if show_by_project:
        query += ", project_name"
    query += """
        FROM commit_stat
        WHERE 1=1
    """
    filters = []
    params = []

    # 时间范围过滤
    if start_date and end_date:
        query += " AND commit_date BETWEEN %s AND %s"
        params.extend([start_date, end_date])
    elif start_date:  # 只传了开始时间
        query += " AND commit_date >= %s"
        params.append(start_date)
    elif end_date:  # 只传了结束时间
        query += " AND commit_date <= %s"
        params.append(end_date)

    # 项目名称过滤
    if project_name:
        query += " AND project_name = %s"
        params.append(project_name)
    # 作者姓名(或Email)搜索
    if author_name:
        # 假设这里既可以按名称也可以按邮件搜索
        query += " AND (author_name LIKE %s OR author_name LIKE %s)"
        like_val = f"%{author_name}%"
        params.extend([like_val, like_val])

    # 按项目分组，且按总新增行数降序排序
    if show_by_project:
        query += """
            GROUP BY author_name, project_name
            ORDER BY project_name, total_lines_added DESC
        """
    else:
        query += """
            GROUP BY author_name
            ORDER BY total_lines_added DESC
        """

    connection = get_db_connection()
    results = []
    try:
        with connection.cursor() as cursor:
            cursor.execute(query, params)
            results = cursor.fetchall()
    finally:
        connection.close()

    return jsonify(results)


# =====================================
# 2. 个人提交记录 - 页面与数据接口
# =====================================

@app.route('/personal_submissions')
def personal_submissions():
    """
    返回个人提交记录页面（前端页面）。
    """
    return render_template('personal_submissions.html')


@app.route('/api/project_names', methods=['GET'])
def project_names():
    """
    获取所有的项目名称。
    """
    connection = get_db_connection()
    project_names = []
    try:
        with connection.cursor() as cursor:
            cursor.execute("SELECT project_name FROM project_names ORDER BY project_name")
            project_names = cursor.fetchall()
    finally:
        connection.close()

    return jsonify([project['project_name'] for project in project_names])


@app.route('/api/personal_submissions_data', methods=['GET'])
def personal_submissions_data():
    """
    个人提交记录的数据接口，可支持：
    - 时间范围（start_date, end_date）
    - 提交消息搜索（commit_message）
    """
    start_date = request.args.get('start_date', '')
    end_date = request.args.get('end_date', '')
    commit_message = request.args.get('commit_message', '')
    author_name = request.args.get('author_name', '')

    query = """
        SELECT
            commit_id,
            lines_added,
            lines_deleted,
            commit_date,
            author_name,
            project_name,
            commit_message
        FROM commit_stat
        WHERE 1=1
    """
    params = []

    # 时间范围
    if start_date and end_date:
        query += " AND commit_date BETWEEN %s AND %s"
        params.extend([start_date, end_date])
    elif start_date:
        query += " AND commit_date >= %s"
        params.append(start_date)
    elif end_date:
        query += " AND commit_date <= %s"
        params.append(end_date)

    # 提交消息搜索
    if commit_message:
        query += " AND commit_message LIKE %s"
        params.append(f"%{commit_message}%")

    # 如果想固定看某个人的提交，可以加一个author_name参数
    if author_name:
        query += " AND author_name = %s"
        params.append(author_name)

    query += " ORDER BY commit_date DESC"

    connection = get_db_connection()
    results = []
    try:
        with connection.cursor() as cursor:
            cursor.execute(query, params)
            results = cursor.fetchall()
    finally:
        connection.close()

    return jsonify(results)


# -----------------------------
# 启动服务器
# -----------------------------
if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5001)
